package gavdi.dk.data;

import gavdi.dk.model.Customer;
import gavdi.dk.model.Event;
import gavdi.dk.model.User;
import gavdi.dk.model.AssignedWBS;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DAOAssignedWBS {

	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_USERID, MySQLiteHelper.COLUMN_WBS };

	public DAOAssignedWBS(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}



	public boolean createAssignedWBS(Customer customer, User user) {
		open();
		ContentValues values = new ContentValues();

		values.put(MySQLiteHelper.COLUMN_USERID, user.getUserID());
		values.put(MySQLiteHelper.COLUMN_WBS, customer.getWbs());

		long count = database.insert(MySQLiteHelper.TABLE_ASSIGNEDWBS, null,
				values);
		close();
		if (count == -1)
			return false;
		return true;
	}



//	public ArrayList<AssignedWBS> getAllAssignedWBSbyUserID(String userID) {
//		//// UNDER CONSTRUCTION
//		open();
//		ArrayList<AssignedWBS> assignedwbss = new ArrayList<AssignedWBS>();
//		Cursor cursor = database.query(MySQLiteHelper.TABLE_ASSIGNEDWBS,
//				allColumns,
//				MySQLiteHelper.COLUMN_USERID + " = " + "\""+userID+"\"", null,
//				null, null, null);
//		cursor.moveToFirst();
//		while (!cursor.isAfterLast()) {
//
//			AssignedWBS assignedwbs = cursorToAssignedWBS(cursor);
//			assignedwbss.add(assignedwbs);
//   		 	cursor.moveToNext();
//		}
//		cursor.close();
//		close();
//		return assignedwbss;
//	}
	
	public List<AssignedWBS> getAllAssignedWBSbyUserID(String userID) {
		  open();
		  List<AssignedWBS> assignedwbss = new ArrayList<AssignedWBS>();	  

		  
		  String MY_QUERY3 = 
				"SELECT customer.name, assignedwbs.wbs, assignedwbs.userid " +
				"FROM customer, assignedwbs " +
				"WHERE assignedWBS.wbs = customer.wbs " +
				"AND assignedWBS.userid = \""+userID+"\"";


		  Cursor cursor = database.rawQuery(MY_QUERY3, null);

		  cursor.moveToFirst();

		  while (!cursor.isAfterLast()) {
			  
		  AssignedWBS assignedwbs = cursorToAssignedWBSwithCusName(cursor);
		  
		  assignedwbss.add(assignedwbs);
		  

		  cursor.moveToNext();

		  }

		  // Make sure to close the cursor
		  cursor.close();
		  close();
		  return assignedwbss;

		  }


	public boolean deleteRecentWBS(Customer Customer, User user) {
		open();
		int count = database.delete(
				MySQLiteHelper.TABLE_RECENTWBS,
				MySQLiteHelper.COLUMN_CUSID + " = " + Customer.getId()
						+ " AND " + MySQLiteHelper.COLUMN_USERID + "="
						+ "\""+user.getUserID()+"\"", null);
		close();
		if (count == 0)
			return false;
		return true;
	}

	public boolean deleteAssignedWBS(Customer Customer, User user) {
		open();
		int count = database.delete(
				MySQLiteHelper.TABLE_ASSIGNEDWBS,
				MySQLiteHelper.COLUMN_CUSID + " = " + Customer.getId()
						+ " AND " + MySQLiteHelper.COLUMN_USERID + "="
						+ "\""+user.getUserID()+"\"", null);
		close();
		if (count == 0)
			return false;
		return true;
	}

	public int deleteAllRecentWBS(Customer Customer, User user) {
		open();
		int count = database.delete(
				MySQLiteHelper.TABLE_RECENTWBS,
				MySQLiteHelper.COLUMN_USERID + "="
						+ "\""+user.getUserID()+"\"", null);
		close();

		return count;
	}

	public int deleteAllAssignedWBS(Customer Customer, User user) {
		open();
		int count = database.delete(
				MySQLiteHelper.TABLE_ASSIGNEDWBS,
				MySQLiteHelper.COLUMN_USERID + "="
						+ "\""+user.getUserID()+"\"", null);
		close();
		return count;
	}
	  public boolean existsInRecent(String userID, String customerID ){
		  boolean res = false;
		  open();
		  Cursor cursor = 	database.query(MySQLiteHelper.TABLE_RECENTWBS,
			        		new String[]{MySQLiteHelper.COLUMN_ID}, 
			        		MySQLiteHelper.COLUMN_USERID+"="+"\""+userID+"\""+" AND "+
			        		MySQLiteHelper.COLUMN_CUSID+"="+"\""+customerID+"\"", null, null, null, null);
		  res = cursor.moveToFirst();
		  cursor.close();
		  close();
		  return res;
	  }
	  public boolean existsInAssigned(String userID, String customerID ){
		  boolean res = false;
		  open();
		  Cursor cursor = 	database.query(MySQLiteHelper.TABLE_ASSIGNEDWBS,
			        		new String[]{MySQLiteHelper.COLUMN_ID}, 
			        		MySQLiteHelper.COLUMN_USERID+"="+"\""+userID+"\""+" AND "+
			        		MySQLiteHelper.COLUMN_CUSID+"="+"\""+customerID+"\"", null, null, null, null);
		  res = cursor.moveToFirst();
		  cursor.close();
		  close();
		  return res;
	  }

	  public static AssignedWBS cursorToAssignedWBS(Cursor cursor) {
		  AssignedWBS assignedwbs = new AssignedWBS();
		  assignedwbs.setUserID(cursor.getString(0));
		  assignedwbs.setWBS(cursor.getString(1));

		  
	    return assignedwbs;
	  }
	  
	  
	  public static AssignedWBS cursorToAssignedWBSwithCusName(Cursor cursor) {
		  AssignedWBS assignedwbs = new AssignedWBS();
		  
		  Customer customer = new Customer();
		  customer.setName(cursor.getString(0));
		  
		  assignedwbs.setCustomer(customer);
		  assignedwbs.setUserID(cursor.getString(2));
		  assignedwbs.setWBS(cursor.getString(1));

		  
	    return assignedwbs;
	  }
	  

}
